import os
import numpy as np
import pandas as pd
from pyecharts import Bar, Pie, Line
import sqlite3
import redis

# Create your ana here.

if __name__ == '__main__':

    db_name = '../db.sqlite3'

else:

    db_name = 'db.sqlite3'


class UsersAna:

    def __init__(self):

        self.conn = sqlite3.connect(db_name)
        self.r = redis.Redis(host='192.168.142.129', port=6379, password='1', db='0')
        self.df = pd.read_sql('SELECT * FROM info_csv', self.conn)
        self.df.item_price = self.df.item_price.str.replace('$', '').astype('float64')
        self.df['total'] = self.df.quantity * self.df.item_price

    def series_to_bar(self, atrr, v1, name='test', ):
        bar = Bar(name)
        atrr = [str(i, encoding='utf-8') for i in atrr]
        v1 = [round(float(i), 2) for i in v1]
        bar.add(name, atrr, v1, is_label_show=True, is_datazoom_show=True)
        return bar

    def series_to_pie(self, atrr, v1, name='test'):
        pie = Pie(name)
        atrr = [str(i, encoding='utf-8') for i in atrr]
        v1 = [round(float(i), 2) for i in v1]
        pie.add(name, atrr, v1, is_label_show=False)
        # print(pie)
        return pie

    def series_to_line(self, atrr, v1, name='test'):
        line = Line(name)
        atrr = [str(i, encoding='utf-8') for i in atrr]
        v1 = [round(float(i), 2) for i in v1]
        line.add(name, atrr, v1, mark_point=["average"])
        return line

    def get_total_sales(self):
        sales = self.df.drop('index', axis=1)
        print(sales.head(10))
        sales['total'] = sales.quantity * sales.item_price
        return sales.total.sum()

    def get_most_trade_num(self):
        num = self.df
        a = num.groupby('item_name').sum().quantity.sort_values(ascending=False).index[0]
        return a
        # print(num.item_name.value_counts())
        # return num.item_name.value_counts().head(1).index[0]

    def get_most_many_money(self):
        many = self.df
        # many.item_price = many.item_price.str.replace('$', '').astype('float64')
        # many['total'] = many.quantity * many.item_price
        a = many.groupby('item_name').sum().total.sort_values(ascending=False).index[0]
        return a

    def make_every_sale_num(self):
        attr = self.r.hkeys('every_sale_sale_num')
        v1 = self.r.hvals('every_sale_sale_num')
        if not v1:
            num = self.df
            info = num.groupby('item_name').sum().quantity.sort_values(ascending=False)
            info = info.to_dict()
            self.r.hmset('every_sale_sale_num', info)
            attr = self.r.hkeys('every_sale_sale_num')
            v1 = self.r.hvals('every_sale_sale_num')
        # print(attr)
        # print(v1)
        a = self.series_to_bar(attr, v1, name='每种商品的销售个数')
        return a

    def make_every_sale_money(self):
        attr = self.r.hkeys('make_every_sale_money')
        v1 = self.r.hvals('make_every_sale_money')
        if not v1:
            many = self.df
            info = many.groupby('item_name').sum().total
            info = info.to_dict()
            self.r.hmset('make_every_sale_money', info)
            attr = self.r.hkeys('make_every_sale_money')
            v1 = self.r.hvals('make_every_sale_money')
        # attr = [str(i, encoding='utf-8') for i in attr]
        # v1 = [float(i) for i in v1]
        a = self.series_to_bar(attr, v1, name='每种商品的销售金额')
        return a

    def make_eveny_order_sum(self):
        attr = self.r.hkeys('make_eveny_order_sum')
        v1 = self.r.hvals('make_eveny_order_sum')
        if not v1:
            order = self.df
            info = order.groupby('order_id').sum().total
            info = info.to_dict()
            self.r.hmset('make_eveny_order_sum', info)
            attr = self.r.hkeys('make_eveny_order_sum')
            v1 = self.r.hvals('make_eveny_order_sum')
            # a = self.series_to_bar(info)
            # return a
        a = self.series_to_line(attr, v1, name='每个定单的金额')
        return a

    def get_Chicken_Bowl_most_desc(self):
        desc = self.df
        a = desc[desc.item_name == 'Chicken Bowl'].groupby('choice_description').count().sort_values('index',
                                                                                                     ascending=False).index[
            0]
        return a
        # print(a)
        # print(desc.columns)
        # print(desc.item_name == 'Chicken Bowl')

    def make_pie_Chicken_Bowl_every(self):
        attr = self.r.hkeys('make_pie_Chicken_Bowl_every')
        v1 = self.r.hvals('make_pie_Chicken_Bowl_every')
        if not v1:
            desc = self.df
            info = desc[desc.item_name == 'Chicken Bowl'].groupby('choice_description').count().sort_values('index',
                                                                                                            ascending=False).item_price
            # print(a)
            info = info.to_dict()
            self.r.hmset('make_pie_Chicken_Bowl_every', info)
            attr = self.r.hkeys('make_pie_Chicken_Bowl_every')
            v1 = self.r.hvals('make_pie_Chicken_Bowl_every')
        mm = self.series_to_pie(attr, v1, name='“Chicken Bowl”每种配料选择的次数，及所占比例')
        return mm

    def make_pie_every_trade(self):
        attr = self.r.hkeys('make_pie_every_trade')
        v1 = self.r.hvals('make_pie_every_trade')
        if not v1:
            order = self.df
            info = order.groupby('item_name').sum().total
            info = info.to_dict()
            self.r.hmset('make_pie_every_trade', info)
            attr = self.r.hkeys('make_pie_every_trade')
            v1 = self.r.hvals('make_pie_every_trade')
            # print(info.to_dict())
        a = self.series_to_pie(attr, v1, name='每种商品销售金额在流水总额中所占的比例')
        # print(a)
        # print(order.head())
        return a

    def make_table_every_trade_choice(self):
        table = self.df
        # print(table.columns)
        info = table.groupby(['item_name', 'choice_description']).item_price.min().to_dict()

        info=[list(k)+[v] for k,v in info.items()]
        return info

UsersAna_obj = UsersAna()
# x=UsersAna().make_table_every_trade_choice()
x=UsersAna_obj
x.make_table_every_trade_choice()
























